/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.apt.matrimony.methods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apt.matrimony.beans.AccountDetailBeans;
import org.apt.matrimony.beans.ImageTypesBeans;
import org.apt.matrimony.beans.CasteBeans;
import org.apt.matrimony.beans.CityBeans;
import org.apt.matrimony.beans.CountryBeans;
import org.apt.matrimony.beans.LikingCateBeans;
import org.apt.matrimony.beans.LikingModelBeans;
import org.apt.matrimony.beans.MotherTongueBeans;
import org.apt.matrimony.beans.ProfessionalBeans;
import org.apt.matrimony.beans.ReligionBeans;
import org.apt.matrimony.beans.RequestBeans;
import org.apt.matrimony.db.MyConnection;
import org.apt.matrimony.utils.Tools;

/**
 *
 * @author yoko
 */
public class selects {

    public static List<CountryBeans> selectAllCountries() {
        List<CountryBeans> list = new ArrayList<CountryBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from Countries where Country_Status=1";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                CountryBeans bean = new CountryBeans();
                bean.setCountry_Id(rs.getInt("Country_Id"));
                bean.setCountry_Name(rs.getString("Country_Name"));
                bean.setCountry_Status(rs.getInt("Country_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<CityBeans> selectAllCitiesByCountry(int countryId) {
        List<CityBeans> list = new ArrayList<CityBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from Cities where Country_Id=?";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            stm.setInt(1, countryId);
            rs = stm.executeQuery();
            while (rs.next()) {
                CityBeans bean = new CityBeans();
                bean.setCity_Id(rs.getInt("City_Id"));
                bean.setCity_Name(rs.getString("City_Name"));
                bean.setCountry_Id(rs.getInt("Country_Id"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<ReligionBeans> selectAllReligions() {
        List<ReligionBeans> list = new ArrayList<ReligionBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from Religion_Types where Religion_Type_Status=1";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                ReligionBeans bean = new ReligionBeans();
                bean.setReligion_Id(rs.getInt("Religion_Type_Id"));
                bean.setReligion_Name(rs.getString("Religion_Type_Name"));
                bean.setReligion_Status(rs.getInt("Religion_Type_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<MotherTongueBeans> selectAllMotherTongues() {
        List<MotherTongueBeans> list = new ArrayList<MotherTongueBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from MotherTongues";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                MotherTongueBeans bean = new MotherTongueBeans();
                bean.setMother_Tongue_Id(rs.getInt("Mother_Tongue_Id"));
                bean.setMother_Tongue_Name(rs.getString("Mother_Tongue_Name"));
                bean.setMother_Tongue_Status(rs.getInt("Mother_Tongue_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<ProfessionalBeans> selectAllProfessionals() {
        List<ProfessionalBeans> list = new ArrayList<ProfessionalBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from Professional_Types where Professional_Status=1";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                ProfessionalBeans bean = new ProfessionalBeans();
                bean.setProfessional_Id(rs.getInt("Professional_Id"));
                bean.setProfessional_Name(rs.getString("Professional_Name"));
                bean.setProfessional_Status(rs.getInt("Professional_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<CasteBeans> selectAllCaste() {
        List<CasteBeans> list = new ArrayList<CasteBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from Castes where Caste_Status=1";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                CasteBeans bean = new CasteBeans();
                bean.setCaste_Id(rs.getInt("Caste_Id"));
                bean.setCaste_Name(rs.getString("Caste_Name"));
                bean.setCaste_Status(rs.getInt("Caste_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static int getAccountIdByAccountName(String username) {
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select Account_Id from Accounts where Account_Status=1 and User_Name=?";
        int accountId = 0;
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            stm.setString(1, username);
            rs = stm.executeQuery();
            if (rs.next()) {
                accountId = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return accountId;
    }
    /*public static String getGenderByAccountName(String username){
     Connection cnn=null;
     PreparedStatement stm=null;
     ResultSet rs=null;
     String sql="Select Gender from Accounts where Account_Status=1 and User_Name=?";
     String gender="";
     try {
     cnn=MyConnection.openConnection();
     stm=cnn.prepareCall(sql);
     stm.setString(1, username);
     rs=stm.executeQuery();
     if(rs.next()){
     gender=rs.getString(1);
     }
     } catch (Exception e) {
     System.out.println(e.getMessage());
     } finally{
     MyConnection.closeConnection(cnn, stm, rs);
     }
     return gender;
     } */

    public static String getDateFromAccount(int accountId) {
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select Date_Expired from Accounts where Account_Status=1 and Account_Id=?";
        String date = "";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            stm.setInt(1, accountId);
            rs = stm.executeQuery();
            if (rs.next()) {
                date = rs.getString(1);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return date;
    }

    public static List<RequestBeans> getAllRequest() {
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select * from Requests";
        List<RequestBeans> list = new ArrayList<RequestBeans>();
        try {

            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                RequestBeans bean = new RequestBeans();
                bean.setAccount_Id(rs.getInt("Account_Id"));
                bean.setRequest_Sender_Id(rs.getInt("Request_Sender_Id"));
                bean.setRequest_Text(rs.getString("Request_Text"));
                bean.setRequest_Time(rs.getString("Request_Time"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static String getAllRequestSenderNameById(int senderId) {
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select User_Name from Accounts where Account_Id=? ";
        String senderName = "";
        try {

            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            stm.setInt(1, senderId);
            rs = stm.executeQuery();
            if (rs.next()) {
                senderName = rs.getString(1);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return senderName;
    }

    public static String getGenderByUserName(String username) {
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "Select Gender from Accounts where User_Name='" + username + "' ";
        String senderName = "";
        try {

            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            if (rs.next()) {
                senderName = rs.getString(1);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return senderName;
    }

    public static List<AccountDetailBeans> selectAllAccountDetails(String username) {
        List<AccountDetailBeans> list = new ArrayList<AccountDetailBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sqlMale = "select * from Account_Details"
                + " join Images on Account_Details.Account_Details_Id=Images.Account_Details_Id"
                + " join Image_Types on Images.Image_Type_Id=Image_Types.Image_Type_Id"
                + " where Image_Type_Name='My Profile' and Account_Details.Gender='male'";
        String sqlFemale = "select * from Account_Details"
                + " join Images on Account_Details.Account_Details_Id=Images.Account_Details_Id"
                + " join Image_Types on Images.Image_Type_Id=Image_Types.Image_Type_Id"
                + " where Image_Type_Name='My Profile' and Account_Details.Gender='female'";
        try {
            cnn = MyConnection.openConnection();
            String gender = getGenderByUserName(username);
            if (gender.equalsIgnoreCase("male")) {
                stm = cnn.prepareCall(sqlFemale);
                rs = stm.executeQuery();
                while (rs.next()) {
                    AccountDetailBeans bean = new AccountDetailBeans();
                    bean.setImage_Path(rs.getString("Image_Path"));
                    bean.setFull_Name(rs.getString("Full_Name"));
                    list.add(bean);
                }
            } else {
                stm = cnn.prepareCall(sqlMale);
                rs = stm.executeQuery();
                while (rs.next()) {
                    AccountDetailBeans bean = new AccountDetailBeans();
                    bean.setImage_Path(rs.getString("Image_Path"));
                    bean.setFull_Name(rs.getString("Full_Name"));
                    list.add(bean);
                }
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<LikingCateBeans> selectAllLlikingCates() {
        List<LikingCateBeans> list = new ArrayList<LikingCateBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "select * from Liking_Cates where Liking_Status=1";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                LikingCateBeans bean = new LikingCateBeans();
                bean.setLiking_Id(rs.getInt("Liking_Id"));
                bean.setLiking_Name(rs.getString("Liking_Name"));
                bean.setLiking_Status(rs.getInt("Liking_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<LikingModelBeans> selectAllLlikingModelById(int likingId) {
        List<LikingModelBeans> list = new ArrayList<LikingModelBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "select * from Liking_Models where Liking_Model_Status=1 and Liking_Id='" + likingId + "'";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                LikingModelBeans bean = new LikingModelBeans();
                bean.setLiking_Id(rs.getInt("Liking_Id"));
                bean.setLiking_Model_Name(rs.getString("Liking_Model_Name"));
                bean.setLiking_Model_Status(rs.getInt("Liking_Model_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static List<ImageTypesBeans> selectAllImageTypes() {
        List<ImageTypesBeans> list = new ArrayList<ImageTypesBeans>();
        Connection cnn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "select * from Image_Types where Image_Type_Status=1";
        try {
            cnn = MyConnection.openConnection();
            stm = cnn.prepareCall(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                ImageTypesBeans bean = new ImageTypesBeans();
                bean.setImage_Type_Id(rs.getInt("Image_Type_Id"));
                bean.setImage_Type_Name(rs.getString("Image_Type_Name"));
                bean.setImage_Type_Status(rs.getInt("Image_Type_Status"));
                list.add(bean);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            MyConnection.closeConnection(cnn, stm, rs);
        }
        return list;
    }

    public static void main(String[] args) {

        List<LikingCateBeans> cateList = selects.selectAllLlikingCates();
        for (int i = 0; i < cateList.size(); i++) {
            System.out.println("Liking: "+ cateList.get(i).getLiking_Name());
        }

    }
}
